package tech.yunland.shop.util;

        import java.io.FileNotFoundException;
        import java.io.IOException;
        import java.math.BigDecimal;
        import java.text.SimpleDateFormat;
        import java.util.ArrayList;
        import java.util.Date;
        import java.util.HashMap;
        import java.util.List;

        import org.apache.commons.lang3.StringUtils;
        import org.apache.poi.hssf.usermodel.HSSFCell;
        import org.apache.poi.hssf.usermodel.HSSFCellStyle;
        import org.apache.poi.hssf.usermodel.HSSFDateUtil;
        import org.apache.poi.hssf.usermodel.HSSFPalette;
        import org.apache.poi.hssf.usermodel.HSSFRow;
        import org.apache.poi.hssf.usermodel.HSSFSheet;
        import org.apache.poi.hssf.usermodel.HSSFWorkbook;
        import org.apache.poi.hssf.util.HSSFColor;
        import org.apache.poi.ss.util.CellRangeAddress;

/**
 *
 * @author 李珂<likenamehaojie@hotmail.com>
 * @date    2016年4月26日上午8:49:36
 */
public class ExcelShower {

    @SuppressWarnings("unused")
    private static final long serialVersionUID = -8344971443770122206L;


    /**
     * @param workbook 要转换的Excel表
     * @param style         用户自己定义的样式
     * @param flag          标记转换为<table>表后的id
     * @param _temMax  标记了是否生成表头的标记
     * @return
     */
    public static StringBuffer excleToHtml(HSSFWorkbook workbook
            ,List<String> style
            ,String flag
            ,HashMap<String,String> rowStyles
            ,int _temMax){
        if(style==null){
            style = new ArrayList<String>();
        }
        HSSFSheet sheet;
        StringBuffer lsb = new StringBuffer();
        try {
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                sheet = workbook.getSheetAt(sheetIndex);// 获所有的sheet
                if (workbook.getSheetAt(sheetIndex) != null) {
                    sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet
                    if (sheet != null) {
                        int firstRowNum = sheet.getFirstRowNum()+_temMax; // 第一行
                        int lastRowNum = sheet.getLastRowNum(); // 最后一行
                        // 构造Table
                        lsb.append("<table "+flag+">");
                        for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                            if (sheet.getRow(rowNum) != null) {// 如果行不为空，
                                HSSFRow row = sheet.getRow(rowNum);
                                short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格
                                int lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
                                if(rowStyles!=null&&rowStyles.containsKey(rowNum+""))
                                    lsb.append("<tr style = "+rowStyles.get(rowNum+"")+">");
                                else
                                    lsb.append("<tr>");
                                for (int cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格
                                    HSSFCell cell = row.getCell(cellNum);
                                    if (cell != null) {
                                        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                                            continue;
                                        } else {
                                            StringBuffer tdStyle  = null;
                                            if(rowNum != lastRowNum){
                                                tdStyle = new StringBuffer(
                                                        "<td style=\""+(cellNum>=style.size()?"":style.get(cellNum)));
                                            }
                                            else{
                                                tdStyle = new StringBuffer("<td style=\""+(cellNum>=style.size()?"":style.get(cellNum)));
                                            }
                                            HSSFCellStyle cellStyle = cell
                                                    .getCellStyle();
                                            HSSFPalette palette = workbook
                                                    .getCustomPalette(); // 类HSSFPalette用于求颜色的国际标准形式
                                            HSSFColor hColor = palette
                                                    .getColor(cellStyle
                                                            .getFillForegroundColor());
                                            HSSFColor hColor2 = palette
                                                    .getColor(cellStyle
                                                            .getFont(workbook)
                                                            .getColor());

                                            String bgColor = convertToStardColor(hColor);// 背景颜色

                                            short boldWeight = cellStyle
                                                    .getFont(workbook)
                                                    .getBoldweight(); // 字体粗细
                                            short fontHeight = (short) (cellStyle
                                                    .getFont(workbook)
                                                    .getFontHeight() / 2); // 字体大小
                                            String fontColor = convertToStardColor(hColor2); // 字体颜色
                                            if (bgColor != null
                                                    && !"".equals(bgColor
                                                    .trim())) {
                                                tdStyle.append(" background-color:"
                                                        + bgColor + "; ");
                                            }
                                            //	tdStyle.append("border:1px solid;");
                                            if (fontColor != null
                                                    && !"".equals(fontColor
                                                    .trim())) {
                                                tdStyle.append(" color:"
                                                        + fontColor + "; ");
                                            }
                                            tdStyle.append(" font-weight:"
                                                    + boldWeight + "; ");
                                            tdStyle.append(" font-size: "
                                                    + fontHeight + "%;");
                                            int cellReginCol = getMergerCellRegionCol(
                                                    sheet, rowNum, cellNum); // 合并的列（solspan）
                                            int cellReginRow = getMergerCellRegionRow(
                                                    sheet, rowNum, cellNum);// 合并的行（rowspan）
                                            String align = convertAlignToHtml(cellStyle
                                                    .getAlignment()); //
                                            String vAlign = convertVerticalAlignToHtml(cellStyle
                                                    .getVerticalAlignment());
                                            int width = (int) (sheet
                                                    .getColumnWidth(cellNum) / 35.7); //
                                            tdStyle.append("text-align:"+align+";");

                                            lsb.append(tdStyle + "\"");
                                            lsb.append(" align=\"" + align
                                                    + "\" valign=\"" + vAlign
                                                    //+ "\" width=\"" + width
                                                    + "\" ");
                                            lsb.append(" colspan=\""
                                                    + cellReginCol
                                                    + "\" rowspan=\""
                                                    + cellReginRow + "\"");

                                            lsb.append(">" + (StringUtils.isBlank(getCellValue(cell).toString())?"&nbsp;":getCellValue(cell))
                                                    + "</td>");
                                        }
                                    }
                                }
                                lsb.append("</tr>");
                            }
                        }
                    }
                }
            }
        } catch (FileNotFoundException e) {

        } catch (IOException e) {

        }
        lsb.append("</table>");
        return lsb;
    }


    /**
     * 取得单元格的值
     *
     * @param cell
     * @return
     * @throws IOException
     */
    public static Object getCellValue(HSSFCell cell) throws IOException {
        Object value = "";
        if(cell==null){
            return value;
        }

        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            value = cell.getRichStringCellValue().toString().replace("\n", "<br/>");
            if(value.toString().contains("\r")){
                value  = value.toString().replace("\r", "");
                String _tem = "<textarea disabled=\"disabled\" style=\"width: 100%;\" class=\"canedit  noedit\" rows=\"3\" >"+value+"</textarea>";
                value = _tem;
            }
            if(value.toString().contains("\b")){
                String _tem = "<input disabled=\"disabled\"  class=\"canedit\" value="+value+" />";
                value = _tem;
            }
            //如果该单元格包含可执行项
            if(value.toString().endsWith("operCan")){
                String _tem = value.toString().replace("operCan","");
                String colNames = _tem.substring(0,_tem.lastIndexOf(","));
                String id = _tem.substring(_tem.lastIndexOf(",")+1);
                String ope = "<input type=\"hidden\" id=col"+id+" value ="+colNames+" /><button class = 'button' type=\"button\" name=\"edit\" id="+id+">编辑</button><button class='button' type=\"button\" name=\"save\" id="+id+">保存</button>";
                value = ope;

            }

            if(value.toString().contains("%addline%")){
                value = value.toString().replace("%addline%", "");
            }

        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                value = sdf.format(date);
            } else {
                double value_temp = (double) cell.getNumericCellValue();
                BigDecimal bd = new BigDecimal(value_temp);
                BigDecimal bd1 = bd.setScale(3, BigDecimal.ROUND_HALF_UP);
                value = bd1.doubleValue();

            }
        }
        if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK  )
            value = "";
        if(value==null)
            return "";
        return value;
    }

    /**
     * 判断单元格在不在合并单元格范围内，如果是，获取其合并的列数。
     *
     * @param sheet
     *            工作表
     * @param cellRow
     *            被判断的单元格的行号
     * @param cellCol
     *            被判断的单元格的列号
     * @return
     * @throws IOException
     */
    private static int getMergerCellRegionCol(HSSFSheet sheet, int cellRow,
                                              int cellCol) throws IOException {
        int retVal = 0;
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
            int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
            int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
            int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
            int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
            if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
                if (cellCol >= firstCol && cellCol <= lastCol) {
                    retVal = lastCol - firstCol + 1; // 得到合并的列数
                    break;
                }
            }
        }
        return retVal;
    }

    /**
     * 判断单元格是否是合并的单格，如果是，获取其合并的行数。
     *
     * @param sheet
     *            表单
     * @param cellRow
     *            被判断的单元格的行号
     * @param cellCol
     *            被判断的单元格的列号
     * @return
     * @throws IOException
     */
    private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow,
                                              int cellCol) throws IOException {
        int retVal = 0;
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
            int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
            int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
            int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
            int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
            if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
                if (cellCol >= firstCol && cellCol <= lastCol) {
                    retVal = lastRow - firstRow + 1; // 得到合并的行数
                    break;
                }
            }
        }
        return retVal;
    }

    /**
     * 单元格背景色转换
     *
     * @param hc
     * @return
     */
    private static String convertToStardColor(HSSFColor hc) {
        StringBuffer sb = new StringBuffer("");
        if (hc != null) {
            int a = HSSFColor.AUTOMATIC.index;
            int b = hc.getIndex();
            if (a == b) {
                return null;
            }
            sb.append("#");
            for (int i = 0; i < hc.getTriplet().length; i++) {
                String str;
                String str_tmp = Integer.toHexString(hc.getTriplet()[i]);
                if (str_tmp != null && str_tmp.length() < 2) {
                    str = "0" + str_tmp;
                } else {
                    str = str_tmp;
                }
                sb.append(str);
            }
        }
        return sb.toString();
    }

    /**
     * 单元格小平对齐
     *
     * @param alignment
     * @return
     */
    private static String convertAlignToHtml(short alignment) {
        String align = "left";
        switch (alignment) {
            case HSSFCellStyle.ALIGN_LEFT:
                align = "left";
                break;
            case HSSFCellStyle.ALIGN_CENTER:
                align = "center";
                break;
            case HSSFCellStyle.ALIGN_RIGHT:
                align = "right";
                break;
            default:
                break;
        }
        return align;
    }

    /**
     * 单元格垂直对齐
     *
     * @param verticalAlignment
     * @return
     */
    private static String convertVerticalAlignToHtml(short verticalAlignment) {
        String valign = "middle";
        switch (verticalAlignment) {
            case HSSFCellStyle.VERTICAL_BOTTOM:
                valign = "bottom";
                break;
            case HSSFCellStyle.VERTICAL_CENTER:
                valign = "center";
                break;
            case HSSFCellStyle.VERTICAL_TOP:
                valign = "top";
                break;
            default:
                break;
        }
        return valign;
    }


}